package com.jzdjt.tool;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.jzdjt.entity.PaperEntity;
import com.jzdjt.entity.UserEntity;
import com.jzdjt.entity.VideoEntity;

public class DBUtil2 {
	// 这里可以设置数据库名称
 	private static String IP = "172.20.76.96";
	private static String USERNAME = "sa";
	private static String PWD = "123456";
	private static String DBName = "Paper";
	private static String PORT = "1433";
	private static Connection conn = null;
	// 静态代码块（将加载驱动、连接数据库放入静态块中）
	/*
	 * static{ try {
	 * 
	 * //1.加载驱动程序 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	 * //2.获得数据库的连接
	 * conn=(Connection)DriverManager.getConnection(URL,USER,PASSWORD); } catch
	 * (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException
	 * e) { e.printStackTrace(); } } //对外提供一个方法来获取数据库连接 public static Connection
	 * getConnection(){ return conn; }
	 */

	private static Connection getSQLConnection() {
		/* Connection conn = null; */

		try {
			/*Properties prop = new Properties();
			InputStream in = new DBUtil2().getClass().getResourceAsStream("/dbconfig.properties");
			prop.load(in);
			USERNAME = prop.getProperty("username");
			PWD = prop.getProperty("password");
			DBName = prop.getProperty("dbname");
			IP = prop.getProperty("ip");
			PORT = prop.getProperty("port");*/
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			//com.mysql.jdbc.Driver
			//Class.forName("com.mysql.jdbc.Driver");
			// 加上 useunicode=true;characterEncoding=UTF-8 防止中文乱码
			//jdbc:mysql://localhost:3306/RUNOOB
			conn = DriverManager.getConnection("jdbc:sqlserver://" + IP + ":" + PORT + ";DatabaseName="
					+ DBName + ";useunicode=true;characterEncoding=UTF-8", USERNAME, PWD);
			//conn = (Connection) DriverManager.getConnection("jdbc:mysql://" + IP + ":" + PORT + "/" + DBName+"?useUnicode=true&characterEncoding=utf-8&useSSL=false" , USERNAME, PWD);
		/*} catch (IOException e) {
			e.printStackTrace();
*/
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	// 查询
	public List<String> select(String sql) {
		List<String> result = new ArrayList<>();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getSQLConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				// 如果对象中有数据，就会循环打印出来
				String s1 = rs.getString("col1");
				String s2 = rs.getString("col2");
				String s3 = rs.getString("col3");
				String s4 = rs.getString("col4");
				String s5 = rs.getString("col5");
				String s6 = rs.getString("col6");
				result.add(s1);
				result.add(s2);
				result.add(s3);
				result.add(s4);
				result.add(s5);
				result.add(s6);
			}
			closeAllByst(conn, stmt, rs);
			return result;
		} catch (SQLException ex) {
			closeAllByst(conn, stmt, rs);
			return result;
		}
	}

	// 插入和删除
	public Boolean excuteSql(String sql) throws SQLException {
		// 成功-返回false
		Boolean retstats = false;
		Statement stmt = null;
		try {
			conn = getSQLConnection();
			stmt = conn.createStatement();
			retstats = stmt.execute(sql);
			closeAllByst(conn, stmt);
			return retstats;
		} catch (SQLException ex) {
			closeAllByst(conn, stmt);
			return false;
		}
	}

	// 更新
	public Boolean update(String sql) throws SQLException {
		// 返回序号
		Boolean retstats = false;
		Statement stmt = null;
		try {
			conn = getSQLConnection();
			stmt = conn.createStatement();
			int executeUpdate = stmt.executeUpdate(sql);
			if (executeUpdate > 0)
				retstats = true;
			System.out.println(executeUpdate);
			closeAllByst(conn, stmt);
			return retstats;
		} catch (SQLException ex) {
			closeAllByst(conn, stmt);
			return false;
		}
	}

	// 删除
	public void delete(String sql) throws SQLException {
		// 成功-返回false
		Statement stmt = conn.createStatement();
		boolean execute = stmt.execute(sql);
		System.out.println(execute);
	}

	// 测试用例
	public static void main(String[] args) throws Exception {
		DBUtil2 d = new DBUtil2();
		// 3.通过数据库的连接操作数据库，实现增删改查
		Statement stmt = conn.createStatement();
		// ResultSet executeQuery(String sqlString)：执行查询数据库的SQL语句
		// ，返回一个结果集（ResultSet）对象。
		// d.insert(stmt);
		// d.update(stmt);
		/* d.delete(stmt); */

	}

	private void closeAllByst(Connection con, Statement st) {
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private void closeAllByst(Connection con, Statement st, ResultSet rst) {
		if (rst != null) {
			try {
				rst.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public List<UserEntity> QueryFromStudent(String sql) {
		List<UserEntity> result = new ArrayList<>();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getSQLConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				UserEntity ue = new UserEntity();
				ue.setStudentID(rs.getString("studentID"));
				ue.setName(rs.getString("name"));
				ue.setGender(rs.getString("gender"));
				ue.setProvince(rs.getString("province"));
				ue.setCity(rs.getString("city"));
				ue.setCounty(rs.getString("county"));
				ue.setSchool(rs.getString("school"));
				ue.setGrade(rs.getString("grade"));
				ue.setClassName(rs.getString("class"));
				ue.setPassword(rs.getString("password"));
				ue.setPhoneNumber(rs.getString("phoneNumber"));
				ue.setSubjects(rs.getString("subjects"));
				result.add(ue);
			}
			closeAllByst(conn, stmt, rs);
			return result;
		} catch (SQLException ex) {

			ex.printStackTrace();
			closeAllByst(conn, stmt, rs);
			return result;
		}
	}

	 

	public List<Map<String, Object>> QueryMapFromVideo(String sql) {
		List<Map<String, Object>> result = new ArrayList<>();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getSQLConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("videoID", rs.getString("videoID"));
				map.put("videoName", "     " + rs.getString("videoName"));
				map.put("videoFileName", rs.getString("videoFileName").toString());
				map.put("grade", rs.getString("grade"));
				map.put("videoXK", rs.getString("videoXK"));
				map.put("videoTeacher", rs.getString("videoTeacher"));
				map.put("videoTime", rs.getString("videoTime"));
				map.put("desc", "  简介： " + rs.getString("grade") + "  " + rs.getString("videoXK") + "  "
						+ rs.getString("videoTime"));
				result.add(map);
			}
			closeAllByst(conn, stmt, rs);
			return result;
		} catch (SQLException ex) {
			closeAllByst(conn, stmt, rs);
			return result;
		}
	}

	public List<VideoEntity> QueryFromVideo(String sql) {
		List<VideoEntity> result = new ArrayList<>();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getSQLConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				VideoEntity ve = new VideoEntity();
				ve.setVideoID(rs.getString("videoID"));
				ve.setVideoName(rs.getString("videoName"));
				ve.setVideoFileName(rs.getString("videoFileName"));
				ve.setGrade(rs.getString("grade"));
				ve.setVideoXK(rs.getString("videoXK"));
				ve.setVideoTeacher(rs.getString("videoTeacher"));
				ve.setVideoTime(rs.getString("videoTime"));
				ve.setVideoStatus(rs.getString("videoStatus"));
				ve.setDesc("  简介： " + rs.getString("grade") + "  " + rs.getString("videoXK") + "  "
						+ rs.getString("videoTime"));
				result.add(ve);
			}
			closeAllByst(conn, stmt, rs);
			return result;
		} catch (SQLException ex) {
			closeAllByst(conn, stmt, rs);
			return result;
		}
	}

	public List<PaperEntity> QueryFromPaper(String sql) {
		List<PaperEntity> result = new ArrayList<>();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getSQLConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				PaperEntity pde = new PaperEntity();
				pde.setPaperID(rs.getString("paperID"));
				pde.setPaperName(rs.getString("paperName"));
				pde.setPaperXK(rs.getString("paperXK"));
				pde.setPaperGrade(rs.getString("paperGrade"));
				pde.setPaperStatue(rs.getString("paperStatue"));
				pde.setPaperIntro(rs.getString("paperIntro"));
				pde.setPaperDate(rs.getString("paperDate"));
				pde.setPaperType(rs.getString("paperType"));
				pde.setAnswer(rs.getString("Answer"));
				pde.setLimitTime(rs.getString("LimitTime"));
				pde.setExamDesc("  简介： " +rs.getString("paperGrade")+"  "+rs.getString("paperXK")+"  "+rs.getString("paperDate"));
				result.add(pde);
			}
			closeAllByst(conn, stmt, rs);
			return result;
		} catch (SQLException ex) {
			closeAllByst(conn, stmt, rs);
			return result;
		}
	}

	public List<Map<String, Object>> QueryFromMapPaper(String sql) {
		List<Map<String, Object>> result = new ArrayList<>();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getSQLConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("paperID", rs.getString("paperID"));
				map.put("paperName", "   " + rs.getString("paperName"));
				map.put("paperXK", rs.getString("paperXK"));
				map.put("paperGrade", rs.getString("paperGrade"));
				map.put("paperStatue", rs.getString("paperStatue"));
				map.put("paperIntro", rs.getString("paperIntro"));
				map.put("paperDate", rs.getString("paperDate"));
				map.put("paperType", rs.getString("paperType"));
				map.put("Answer", rs.getString("Answer"));
				map.put("LimitTime",rs.getString("LimitTime"));
				map.put("paperDesc", "  " + rs.getString("paperGrade") + "  " + rs.getString("paperXK") + "  "
						+ rs.getString("paperDate"));
				result.add(map);
			}
			closeAllByst(conn, stmt, rs);
			return result;
		} catch (SQLException ex) {
			closeAllByst(conn, stmt, rs);
			return result;
		}

	}

}